<?php

class AuthorDAO
{
        private $conn;
        private $databaseSelected;


        function __construct()
        {
                $this->conn = mysql_connect();
        $this->databaseSelected = mysql_select_db(DB_NAME);
        
        if(!$this->conn || !$this->databaseSelected)
        {
            die('Database connection error in AuthorDAO');
        }
        }


        function findById($id)
        {
                $qry = '
                                SELECT 
                                        *
                                FROM 
                                        doodle
                                WHERE
                                        iddoodle = '.$id.'
                                ';
        $result = mysql_query($qry, $this->conn);
        if(!$result)
        {
            die('Could not retrieve random doodle DoodleDAO');
        }
                $doodles = array();

                $row = mysql_fetch_array($result);

                $author= new Author($row['iddoodle'], $row['student_name'], $row['year_level'], $row['idschool']);
        return $author;
        }
        function findYearsBySchool($schoolId)
        {
                $qry = '
                                SELECT 
                                        *
                                FROM 
                                        doodle
                                WHERE
                                        idschool = '.$schoolId.'
                                GROUP BY
                                        year_level
                                ORDER BY
                                        year_level
                                ';
        $result = mysql_query($qry, $this->conn);
        if(!$result)
        {
            die('Could not retrieve years in AuthorDAO');
        }
                $years = array();

                while($row = mysql_fetch_array($result))
                {
                        $years []= $row['year_level'];
        }
        return $years;
        }
        function findBySchoolYear($schoolId, $year)
        {
                $qry = '
                                SELECT 
                                        *
                                FROM 
                                        doodle
                                        INNER JOIN
                                        school
                                        ON
                                        doodle.idschool = school.idschool
                                WHERE
                                        doodle.idschool = '.$schoolId.'
                                        AND
                                        year_level = "'.$year.'"
                                        AND image_filename IS NOT NULL
                                ';
        $result = mysql_query($qry, $this->conn);
        if(!$result)
        {
            die('Could not retrieve authors in AuthorDAO<pre>'.$qry.'</pre>');
        }

                $authors = array();
                while($row = mysql_fetch_array($result))
                {
                        $authors []= new Author($row['iddoodle'], $row['student_name'], $row['year_level'], $row['idschool']);
                }
                return $authors;
        }
        function getDoodles($author)
        {
                $qry = '
                                SELECT 
                                        *
                                FROM 
                                        doodle
                                        INNER JOIN
                                        abstract
                                        ON
                                        doodle.iddoodle = abstract.iddoodle
                                WHERE
                                        doodle.iddoodle = '.$author->id.'
                                ';
        $result = mysql_query($qry, $this->conn);
        if(!$result)
        {
            die('Could not retrieve doodle list in DoodleDAO');
        }
                $doodles = array();
                while($row = mysql_fetch_array($result))
                {
                        $doodles[]= new Doodle($row['iddoodle'], "doodle name not stored", $row['abstract'], $row['image_filename'], $row['image_filename'], $row['iddoodle']);
                }
        return $doodles;
        }
}
?>
